package com.example.work1.server.mapper;

import com.example.work1.server.pojo.Student;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper {
    @Insert("INSERT INTO students (id, name, sex, major_id, enrollment_date, score,is_deleted) VALUES (#{id}, #{name}, #{sex}, #{majorId}, #{enrollmentDate}, #{score},false)")
    void insertStudent(Student student);

    @Select("SELECT * FROM students join majors WHERE students.major_id=majors.id and students.id = #{id} and is_deleted = false")
    Student getStudentById(String id);

    @Select("SELECT * FROM students where is_deleted = false")
    List<Student> getAllStudents();

    void updateStudent(Student student);

    @Update("UPDATE students SET is_deleted = true WHERE id = #{id}")
    void deleteStudent(String id);

    @Select("SELECT COUNT(*) FROM students")
    int getStudentCount();

    @Select("SELECT * FROM students join majors  WHERE students.major_id=majors.id and score = (SELECT MAX(score) FROM students WHERE is_deleted = false) AND is_deleted = false")
    List<Student> getTopScoringStudents();

    @Select("SELECT * FROM students join majors  WHERE students.major_id=majors.id and score = (SELECT MIN(score) FROM students WHERE is_deleted = false) AND is_deleted = false")
    List<Student> getLowestScoringStudents();

    @Select("SELECT enrollment_date, GROUP_CONCAT(name SEPARATOR ', ') AS student_names " +
            "FROM students WHERE is_deleted = false " +
            "GROUP BY enrollment_date")
    List<Map<String, Object>> getStudentsGroupedByEnrollmentDate();

}
